package tma.sdc.batch8.mobile10.shareboard.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.codehaus.jettison.json.JSONArray;

import tma.sdc.batch8.mobile10.shareboard.util.ToJSON;


/** 
 * Having all sql/database code in one package makes it easier to maintain and audit
 * but increase complexity.
 * 
 * @author anhle
 */
public class StudentDAO {

	/**
	 * This method will search for a student from the "student" table.
	 * By using prepareStatement and the ?, we are protecting against sql injection
	 * 
	 * Never add parameter straight into the prepareStatement
	 * 
	 * @param name - name student
	 * @return - json array of the results from the database
	 * @throws Exception
	 */
	public JSONArray queryStudentName(String name) throws Exception {
		
		PreparedStatement query = null;
		Connection conn = null;
		
		ToJSON converter = new ToJSON();
		JSONArray json = new JSONArray();
		
		try {
			conn = MySQLHelper.getConnection();
			query = conn.prepareStatement("SELECT student_id, username, avatar_link " +
											"FROM student " +
											"WHERE UPPER(username) = ? ");
			
			query.setString(1, name.toUpperCase()); //protect against sql injection
			ResultSet rs = query.executeQuery();
			
			json = converter.toJSONArray(rs);
			
			query.close(); //close connection
			conn.close();
		}
		catch(SQLException sqlError) {
			sqlError.printStackTrace();
			return json;
		}
		catch(Exception e) {
			e.printStackTrace();
			return json;
		}
		
		return json;
	}

	public JSONArray queryStudentLogin(String username, String password) {
		
		PreparedStatement query = null;
		Connection conn = null;
		
		ToJSON converter = new ToJSON();
		JSONArray json = new JSONArray();
		
		try {
			conn = MySQLHelper.getConnection();
			query = conn.prepareStatement("SELECT student_id, username, avatar_link " +
											"FROM student " +
											"WHERE username = ? AND password = ?");
			
			query.setString(1, username); //protect against sql injection
			query.setString(2, password); //protect against sql injection
			
			
			ResultSet rs = query.executeQuery();
			
			json = converter.toJSONArray(rs);
			
			query.close(); //close connection
			conn.close();
		}
		catch(SQLException sqlError) {
			sqlError.printStackTrace();
			return json;
		}
		catch(Exception e) {
			e.printStackTrace();
			return json;
		}
		
		return json;
	}
	
	
}
